package h2demo;

import java.util.List;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.h2.jdbcx.JdbcDataSource;

public class UtilsTest {
	
	private static DataSource getDataSource() {
		JdbcDataSource ds = new JdbcDataSource();
		ds.setURL("jdbc:h2:~/h2demo");
		return ds;
	}
	
	private static void createTable() throws SQLException {
		String sql = "create table if not exists user("
				+ "id int auto_increment primary key,"
				+ "username varchar(128) unique not null,"
				+ "password varchar(128) not null,"
				+ "filename varchar(128) not null,"
				+ "sex char(1) not null,"
				+ "interests varchar(128) )";
		new QueryRunner(getDataSource()).execute(sql);
	}
	
	private static void insertData() throws SQLException {
		String drop = "delete from user";
		String sql1 ="insert into user(username, password, filename, sex, interests) values('tom', '1234', 'a.png', 'M', 'books')";
		String sql2 ="insert into user(username, password, filename, sex, interests) values('mike', '2345', 'b.png', 'F', 'balls')";
		QueryRunner runner = new QueryRunner(getDataSource());
		runner.execute(drop);
		runner.execute(sql1);
		runner.execute(sql2);
	}
	
	private static void select() throws SQLException {
		String sql = "select username, password, filename, sex, interests from user";
		QueryRunner runner = new QueryRunner(getDataSource());
		List<User> users = runner.query(sql, new BeanListHandler<>(User.class));
		for(User user : users) {
			System.out.println(user);
		}
	}
	
	public static void main(String[] args) throws SQLException{
		createTable();
		insertData();
		select();
	}
}